﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BCCommon.Enums;
using BCEntity.Common.UserBankCard;
using Dapper;

namespace BCData.Common.UserBankCard
{
    public class UserBankCardData : IUserBankCardData
    {
     
        private const string QUERY_SQL = @"SELECT C.UserBankCardId,C.UserId,C.WalletId,C.BankCardNo,C.BankName,C.BankKeyCode,C.IsRealnameValidation,C.RealnameValidationTime,C.CreateTime,
                                            U.Phone,U.UserName,U.IdentityCardNo,
											D.Name AS  BankCode,I.ItemValue AS BankImage																
                                            FROM UserBankCard C
                                            LEFT JOIN User U  ON C.UserId=U.UserId																						
										    LEFT JOIN dictionaryreference D  ON D.SearchKey= c.BankKeyCode
										    LEFT JOIN dictionaryreference I  ON D.Name = I.Name AND I.ParentName='BankImage'";
        private readonly ISqlCommand command;

        public UserBankCardData(ISqlCommand command)
        {
            this.command = command;
        }

        public UserBankCardEntity Create(UserBankCardEntity userBankCardEntity)
        {
            string insertSql = command.GetInsertSql("UserBankCard", new string[] { "UserId", "WalletId", "BankCardNo", "BankName", "BankKeyCode", "IsRealnameValidation", "RealnameValidationTime" });
            string sql = string.Format(@"{0} {1} Where C.UserBankCardId=LAST_INSERT_ID() AND I.ParentName='BankImage';", insertSql, QUERY_SQL);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userBankCardEntity.UserId, DbType.Int64, size: 20);
            parameters.Add("?WalletId", userBankCardEntity.WalletId, DbType.Int64,size:20);
            parameters.Add("?BankCardNo", userBankCardEntity.BankCardNo, DbType.AnsiString,size:50);
            parameters.Add("?BankName", userBankCardEntity.BankName, DbType.AnsiString,size:30);
            parameters.Add("?BankKeyCode", userBankCardEntity.BankKeyCode, DbType.AnsiString,size:150);
            parameters.Add("?IsRealnameValidation", userBankCardEntity.IsRealnameValidation, DbType.Boolean);
            parameters.Add("?RealnameValidationTime", userBankCardEntity.RealnameValidationTime, DbType.DateTime);
            return command.QueryEntity<UserBankCardEntity>(sql, CommandType.Text, parameters);

        }
        public int Delete(long userBankCardId)
        {
            string deleteSql = "DELETE FROM UserBankCard WHERE UserBankCardId=?UserBankCardId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserBankCardId", userBankCardId, DbType.Int64, size: 20);
            return command.Execute(deleteSql, CommandType.Text, parameters);
        }


        public bool ExistsByBankCardId(long userBankCardId)
        {
            string sql = @"SELECT 1 FROM UserBankCard WHERE UserBankCardId=?UserBankCardId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserBankCardId", userBankCardId, DbType.Int64, size: 20);
            object result = this.command.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null;
        }

        public bool ExistsByBankCardNo(string bankCardNo)
        {
            string sql = @"SELECT 1 FROM UserBankCard WHERE BankCardNo=?BankCardNo;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BankCardNo", bankCardNo, DbType.AnsiString, size: 50);
            object result = this.command.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null;
        }

        public UserBankCardEntity Get(long userBankCardId)
        {
            string sql = @"{0} WHERE C.UserBankCardId=?UserBankCardId AND I.ParentName='BankImage';";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserBankCardId", userBankCardId, DbType.Int64, size: 20);
            return this.command.QueryEntity<UserBankCardEntity>(string.Format(sql, QUERY_SQL), CommandType.Text, parameters);
        }


        public IEnumerable<UserBankCardEntity> List(string userName, string phone, string identityCardNo, string bankCardNo, bool? ascending, int pageIndex, int pageSize, out int dataCount, long? userId, long? walletId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(userName))
            {
                wheres.Add("U.UserName=?UserName");
                parameters.Add("?UserName", userName, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("U.Phone=?Phone");
                parameters.Add("?Phone", phone, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(identityCardNo))
            {
                wheres.Add("U.IdentityCardNo=?IdentityCardNo");
                parameters.Add("?IdentityCardNo", identityCardNo, DbType.AnsiString, size: 18);
            }
            if (!string.IsNullOrWhiteSpace(bankCardNo))
            {
                wheres.Add("C.BankCardNo=?BankCardNo");
                parameters.Add("?BankCardNo", bankCardNo, DbType.AnsiString, size: 50);
            }
            if (userId.HasValue)
            {
                wheres.Add("C.UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64, size: 20);
            }
            if (walletId.HasValue)
            {
                wheres.Add("C.WalletId=?WalletId");
                parameters.Add("?WalletId", walletId, DbType.Int64, size: 20);
            }
            string sql = string.Format(@"{0} {1} ORDER BY C.CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM UserBankCard C
                                            LEFT JOIN User U  ON C.UserId=U.UserId																						
										    LEFT JOIN dictionaryreference D  ON D.SearchKey= c.BankKeyCode
										    LEFT JOIN dictionaryreference I  ON D.Name = I.Name  AND I.ParentName='BankImage' {1}",
            QUERY_SQL,
            wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
            (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
             pageSize,
             ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var result = this.command.QueryMultiple<UserBankCardEntity, int>(sql, CommandType.Text, parameters);
            dataCount = result.Item2;
            return result.Item1;
        }

        public int RealNameValidation(long userBankCardId, DateTime realnameValidationTime)
        {
            string updateSql = @"UPDATE UserBankCard set 
                                IsRealnameValidation = ?IsRealnameValidation,
                                RealnameValidationTime = ?RealnameValidationTime
                                where UserBankCardId = ?UserBankCardId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsRealnameValidation", true, DbType.Boolean);
            parameters.Add("?RealnameValidationTime", realnameValidationTime, DbType.DateTime);
            parameters.Add("?UserBankCardId", userBankCardId, DbType.Int64, size: 20);            
            return this.command.Execute(updateSql, CommandType.Text, parameters);
        }

        public UserBankCardEntity Update(UserBankCardEntity userBankCardEntity)
        {
            string updateSql = @"UPDATE UserBankCard set 
                                UserId = ?UserId,
                                UserId = ?WalletId,
                                BankCardNo = ?BankCardNo,
                                BankName = ?BankName,
                                BankKeyCode = ?BankKeyCode,
                                IsRealnameValidation = ?IsRealnameValidation,
                                RealnameValidationTime = ?RealnameValidationTime
                                where UserBankCardId = ?UserBankCardId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userBankCardEntity.UserId, DbType.Int64, size: 20);
            parameters.Add("?WalletId", userBankCardEntity.WalletId, DbType.Int64, size: 20);
            parameters.Add("?BankCardNo", userBankCardEntity.BankCardNo, DbType.AnsiString, size: 50);
            parameters.Add("?BankName", userBankCardEntity.BankName, DbType.AnsiString, size: 30);
            parameters.Add("?BankKeyCode", userBankCardEntity.BankKeyCode, DbType.AnsiString, size: 150);
            parameters.Add("?UserBankCardId", userBankCardEntity.UserBankCardId, DbType.Int64, size: 20);
            string sql = string.Format(@"{0} {1} Where C.UserBankCardId=?UserBankCardId AND I.ParentName='BankImage';", updateSql, QUERY_SQL);
            return this.command.QueryEntity<UserBankCardEntity>(sql, CommandType.Text, parameters);
        }
    }
}
